package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;


public class DBController {

	private static Connection conn = null;
	private static String url = "jdbc:mysql://localhost:3306/";
	private static String driver = "com.mysql.jdbc.Driver";
	
	// configuration settings
	private static String dbName = "ReservationSysDB";
	private static String username = "root";
	private static String password = "root";
	
	// establish connection to database
	// all methods requiring connection hooks up with database automatically
	private static void connect() {
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(url + dbName, username, password);
		} catch (Exception e) {
			System.out.println("Error at method connect() of DBController.");
			e.printStackTrace();
		}
	}
	
	// close connection to database to free up resources
	// calling method needs to invoke 'disconnect()' manually
	public static void disconnect() {
		try {
			if( conn != null )
				conn.close();
		} catch (Exception e) {
			System.out.println("Error at method disconnect() of DBController.");
			e.printStackTrace();
		}
	}
	
	// retrieve records
	public static ResultSet retrieve(String tableName, String columns, String condition) {
		ResultSet rs = null;
		String queryStr = "";
		String columnString = "*";
		
		// select specific columns
		if( columns != null )
			columnString = columns;
		
		// filter records by condition
		if (condition == null)
			queryStr = "Select " + columnString + " from " + tableName;
		else
			queryStr = "Select " + columnString + " from " + tableName + " where " + condition;
				
		try {
			connect();
			Statement st = conn.createStatement();
			rs = st.executeQuery(queryStr);
		} catch (Exception e) {
			System.out.println("Error at method retrieve() of DBController.");
			e.printStackTrace();
		}
		
		return rs;
	}
	public static ResultSet retrieve(String str) {
		ResultSet rs = null;
		String queryStr = "";
		String columnString = "*";
		
		// select specific columns
		queryStr=str;
		try {
			connect();
			Statement st = conn.createStatement();
			rs = st.executeQuery(queryStr);
		} catch (Exception e) {
			System.out.println("Error at method retrieve() of DBController.");
			e.printStackTrace();
		}
		
		return rs;
	}
	public static int save(String queryStr) {
		int recordCount=0;
			
		try {
			connect();
			Statement st = conn.createStatement();
			recordCount = st.executeUpdate(queryStr);
		} catch (Exception e) {
			System.out.println("Error at method save of DBController.");
			e.printStackTrace();
		}
		
		return recordCount;
	}
	public static int saveAndGetId(String queryStr) {
		int generateKey=0;
			
		try {
			connect();
			Statement st = conn.createStatement();
			generateKey = st.executeUpdate(queryStr);
			ResultSet rs= st.getGeneratedKeys();
			 rs = st.getGeneratedKeys();
		      while (rs.next()) {
		        ResultSetMetaData rsMetaData = rs.getMetaData();
		        int columnCount = rsMetaData.getColumnCount();

		        for (int i = 1; i <= columnCount; i++) {
		          String key = rs.getString(i);
		          try{
		          generateKey=Integer.parseInt(key);
		          }catch(Exception ex){}
		        }
		      }
			
		} catch (Exception e) {
			System.out.println("Error at method save of DBController.");
			e.printStackTrace();
		}
		
		return generateKey;
	}
	public static ResultSet retrieveRatesCount(String tableName, String columns, String condition , String groupby ) {
		ResultSet rs = null;
		String queryStr = "";
		String columnString = "*";
		
		// select specific columns
		if( columns != null )
		columnString = columns;
		
		// query with table join
		queryStr = "select count(*) as roomCount , Room.TypeID from Room Inner join RoomType ON Room.TypeID = RoomType.ID INNER JOIN rates ON rates.TypeID = RoomType.ID";
		// filter records by condition
		if (condition != null) 
		
		queryStr =  queryStr + " where " + condition;		
		// add ORDER BY 
		if (groupby != null )
		queryStr = queryStr + " Group By " + groupby ;
		queryStr = queryStr + " Order By " + groupby ;
		
		try {
			connect();
			Statement st = conn.createStatement();
			rs = st.executeQuery(queryStr);
			} catch (Exception e) {
			System.out.println("Error at method retrieve() of DBController.");
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public static ResultSet retrieveRates(String tableName, String columns, String condition , String orderby ) {
		ResultSet rs = null;
		String queryStr = "";
		String columnString = "*";
		
		// select specific columns
		if( columns != null )
		columnString = columns;
		
		// query with table join
		queryStr = "select Room.* , RoomType.* , rates.*  from Room Inner join RoomType ON Room.TypeID = RoomType.ID INNER JOIN rates ON rates.TypeID = RoomType.ID";
		// filter records by condition
		if (condition != null) 
		
		queryStr =  queryStr + " where " + condition;		
		// add ORDER BY 
		if (orderby != null )
		queryStr = queryStr + " Order By " + orderby + " LIMIT 1";
		
		try {
			connect();
			Statement st = conn.createStatement();
			rs = st.executeQuery(queryStr);
			} catch (Exception e) {
			System.out.println("Error at method retrieve() of DBController.");
			e.printStackTrace();
		}
		
		return rs;
	}
	
}
